﻿using BCCommon.Enums;
using BCData.Common.News.NewsContent;
using BCEntity.Common.News.NewsContent;
using Dapper;
using System;
using System.Collections.Generic;
using System.Data;
using System.Threading.Tasks;
using BCCommon;
namespace BCData.Common.News.NewsContent
{
    /// <summary>
    /// 新闻内容数据处理
    /// </summary>
    public class NewsContentData : INewsContentData
    {
        private const string QUERY_EXISTS = @"SELECT 1 FROM newscontent";
        private const string sql = @"SELECT A.NewsContentId,A.Title,A.SubTitle,A.Content,
                              A.IsDelete,A.CreateTime,A.AdminId,A.ContentCategoryId,A.Enabled,
                              A.ViewsCount,A.IsComment,A.KeyWord,A.Summary,A.ImageUrl,A.Link,A.ReprintCount,A.LikeCount,A.ReplyCount,B.CategoryName,B.CategoryUrlName,C.LoginName
                        FROM newscontent A 
                            LEFT JOIN contentcategory B 
                                ON B.ContentCategoryId = A.ContentCategoryId
                            INNER JOIN admin C
                                ON A.AdminId=C.AdminId";
        private const string viewsql = @"SELECT A.NewsContentId,A.Title,A.SubTitle,A.Content,
                                  A.IsDelete,A.CreateTime,A.AdminId,A.ContentCategoryId,A.ReprintCount,A.LikeCount,A.ReplyCount,A.Enabled,
                                  A.ViewsCount,A.IsComment,A.KeyWord,A.Summary,A.ImageUrl,A.Link,B.ContentCategoryId,B.CategoryName,
                                  B.ParentCategoryId,B.CategoryLevel,B.CategoryIndex,A.CreateTime,C.LoginName
                           FROM newscontent A 
                                INNER JOIN contentcategory B 
                                    ON A.ContentCategoryId = B.ContentCategoryId
                                INNER JOIN admin C
                                    ON A.AdminId=C.AdminId";
        private const string sqlbyID = @"SELECT A.NewsContentId,A.Title,A.SubTitle,A.Content,
                                  A.IsDelete,A.CreateTime,A.AdminId,A.ContentCategoryId,A.Enabled,
                                  A.ViewsCount,A.IsComment,A.KeyWord,A.Summary,A.ImageUrl,A.Link,B.CategoryName,A.ReprintCount,A.LikeCount,A.ReplyCount,
                                  C.LoginName
                            FROM newscontent A 
                                LEFT JOIN contentcategory B ON B.ContentCategoryId = A.ContentCategoryId 
                                INNER JOIN admin C ON A.AdminId=C.AdminId
                            WHERE A.NewsContentId=?NewsContentId";
        protected readonly IDatabaseContext databaseContext;
        public NewsContentData(IDatabaseContext databaseContext)
        {
            this.databaseContext=databaseContext;
        }

        #region 检查新闻内容是否存在
        public async Task<bool> Exists(long newsContentId)
        {
            string sql = string.Format(@"{0} WHERE NewsContentId=?NewsContentId", QUERY_EXISTS);
            DynamicParameters parameters = new DynamicParameters();
            parameters.Add("?NewsContentId", newsContentId, DbType.Int64);
            return await this.databaseContext.Slave.ExecuteScalarAsync(sql, parameters) != null;
        }
        #endregion

        public NewsContentEntity Add(NewsContentEntity entity)
        {
            string addSql = @"INSERT INTO newscontent(Title,SubTitle,Content,CreateTime,AdminId,AdminName,ContentCategoryId,ViewsCount,IsComment,KeyWord,Summary,ImageUrl,Link)
                                VALUES(?Title,?SubTitle,?Content,?CreateTime,?AdminId,?AdminName,?ContentCategoryId,?ViewsCount,?IsComment,?KeyWord,?Summary,?ImageUrl,?Link);
                           SELECT A.NewsContentId,A.Title,A.SubTitle,A.Content,A.CreateTime,A.AdminId,A.ContentCategoryId,A.ViewsCount,A.IsComment,A.KeyWord,A.Summary,
                                    A.ImageUrl,A.Link,B.CategoryName,C.LoginName,A.IsDelete,A.Enabled
                                FROM newscontent A 
                                    INNER JOIN contentcategory B ON B.ContentCategoryId = A.ContentCategoryId
                                    INNER JOIN admin C ON A.AdminId=C.AdminId
                                WHERE A.NewsContentId=LAST_INSERT_ID();";
            DynamicParameters parameters = new DynamicParameters();
            parameters.Add("?Title", entity.Title, DbType.AnsiString, size: 50);
            parameters.Add("?SubTitle", entity.SubTitle, DbType.AnsiString, size: 25);
            parameters.Add("?Content", entity.Content, DbType.String);
            parameters.Add("?CreateTime", DateTime.Now, DbType.DateTime);
            parameters.Add("?AdminId", entity.AdminId, DbType.Int64, size: 20);
            parameters.Add("?AdminName", entity.AdminName, DbType.AnsiString, size: 30);
            parameters.Add("?ContentCategoryId", entity.ContentCategoryId, DbType.Int32, size: 10);
            parameters.Add("?ViewsCount", entity.ViewsCount, DbType.Int32, size: 10);
            parameters.Add("?Summary", entity.Summary, DbType.AnsiString, size: 100);
            parameters.Add("?KeyWord", entity.KeyWord, DbType.AnsiString, size: 20);
            parameters.Add("?ImageUrl", entity.ImageUrl, DbType.AnsiString, size: 1000);
            parameters.Add("?Link", entity.Link, DbType.AnsiString, size: 1000);
            parameters.Add("?IsComment", entity.IsComment, DbType.Boolean);
            return this.databaseContext.Master.QueryEntity<NewsContentEntity>(addSql, parameters,databaseContext.Transaction);
        }

        public int GetListCount(int? contentCategoryId, string title, string keyWord, DateTime? start, DateTime? end, bool fromSim)
        {
            List<string> wheres = new List<string>();
            DynamicParameters parameters = new DynamicParameters();
            wheres.Add("A.Enabled=1");

            if (!string.IsNullOrWhiteSpace(keyWord))
            {
                wheres.Add("A.KeyWord=?KeyWord");
                parameters.Add("?KeyWord", keyWord, DbType.AnsiString);
            }
            if (!string.IsNullOrWhiteSpace(title))
            {
                wheres.Add("A.Title LIKE CONCAT('%',?Title,'%')");
                parameters.Add("?Title", title, DbType.AnsiString);
            }
            if (contentCategoryId.HasValue)
            {
                wheres.Add("A.ContentCategoryId=?ContentCategoryId");
                parameters.Add("?ContentCategoryId", contentCategoryId.Value, DbType.Int32);
            }
            if (start.HasValue && !end.HasValue)
            {
                wheres.Add("A.CreateTime>=?Start");
                parameters.Add("?Start", start.Value, DbType.DateTime);
            }
            else if (!start.HasValue && end.HasValue)
            {
                wheres.Add("A.CreateTime<=?End");
                parameters.Add("?End", end.Value, DbType.DateTime);
            }
            else if (start.HasValue && end.HasValue)
            {
                wheres.Add("A.CreateTime>=?Start AND A.CreateTime<=?End");
                parameters.Add("?Start", start.Value, DbType.DateTime);
                parameters.Add("?End", end.Value, DbType.DateTime);
            }
            if (!fromSim)
            {
                wheres.Add("A.IsDelete=0");
            }
            string newsql = string.Format("SELECT COUNT(1) FROM newscontent A {1}",
                sql,
                wheres.Count > 0 ? "WHERE " + string.Join(" AND ", wheres) : string.Empty);
            return this.databaseContext.Slave.ExecuteScalarAsync<int>(sql, parameters).GetAwaiter().GetResult();
        }

        public IEnumerable<NewsContentEntity> GetPageableList(int? contentCategoryId, string title, string keyWord, DateTime? start, DateTime? end, bool? ascending, bool fromSim, out int dataCount, int pageIndex, int pageSize)
        {
            List<string> wheres = new List<string>();
            DynamicParameters parameters = new DynamicParameters();
            
            if (!string.IsNullOrWhiteSpace(keyWord))
            {
                wheres.Add("A.KeyWord=?KeyWord");
                parameters.Add("?KeyWord", keyWord, DbType.AnsiString);
            }
            if (!string.IsNullOrWhiteSpace(title))
            {
                wheres.Add("A.Title LIKE CONCAT('%',?Title,'%')");
                parameters.Add("?Title", title, DbType.AnsiString);
            }
            if (contentCategoryId.HasValue)
            {
                wheres.Add("A.ContentCategoryId=?ContentCategoryId");
                parameters.Add("?ContentCategoryId", contentCategoryId.Value, DbType.Int32);
            }
            if (start.HasValue && !end.HasValue)
            {
                wheres.Add("A.CreateTime>=?Start");
                parameters.Add("?Start", start.Value, DbType.DateTime);
            }
            else if (!start.HasValue && end.HasValue)
            {
                wheres.Add("A.CreateTime<=?End");
                parameters.Add("?End", end.Value, DbType.DateTime);
            }
            else if (start.HasValue && end.HasValue)
            {
                wheres.Add("A.CreateTime>=?Start AND A.CreateTime<=?End");
                parameters.Add("?Start", start.Value, DbType.DateTime);
                parameters.Add("?End", end.Value, DbType.DateTime);
            }
            if (!fromSim)
            {
                wheres.Add("A.Enabled=1");
                wheres.Add("A.IsDelete=0");
            }
            string newsql = string.Format("{0} {1} Order By A.CreateTime {4} LIMIT {2}, {3}; SELECT COUNT(1) FROM newscontent A {1}",
                sql,
                wheres.Count > 0 ? "WHERE " + string.Join(" AND ", wheres) : string.Empty,
                (pageIndex == 0 ? 0 : pageIndex - 1) * pageSize,
                pageSize,
                ascending.HasValue && ascending.Value ? "ASC" : "DESC");
            var results = this.databaseContext.Slave.QueryMultiple<NewsContentEntity, int>(newsql, parameters);
            dataCount = results.Item2;
            return results.Item1;
        }

        public IEnumerable<NewsContentEntity> GetNewsList(int contentCategoryId)
        {
            List<string> wheres = new List<string>();
            DynamicParameters parameters = new DynamicParameters();
            wheres.Add("A.Enabled=1");
            wheres.Add("A.ContentCategoryId=?ContentCategoryId");
            parameters.Add("?ContentCategoryId", contentCategoryId, DbType.Int32);

            string newsql = string.Format("{0} {1} Order By A.CreateTime DESC; SELECT COUNT(1) FROM newscontent A {1}",
                sql,
                wheres.Count > 0 ? "WHERE " + string.Join(" AND ", wheres) : string.Empty);

            var results = this.databaseContext.Slave.Query<NewsContentEntity>(newsql, parameters);
            return results;
        }

        public IEnumerable<NewsContentEntity> GetNewById(long newsContentId)
        {
            List<string> wheres = new List<string>();
            DynamicParameters parameters = new DynamicParameters();
            wheres.Add("A.Enabled=1");
            wheres.Add("A.NewsContentId=?NewsContentId");
            parameters.Add("?NewsContentId", newsContentId, DbType.Int64);

            string newsql = string.Format("{0} {1} Order By A.CreateTime DESC; SELECT COUNT(1) FROM newscontent A {1}",
                sql,
                wheres.Count > 0 ? "WHERE " + string.Join(" AND ", wheres) : string.Empty);

            var results = this.databaseContext.Slave.Query<NewsContentEntity>(newsql, parameters);
            return results;
        }

        public IEnumerable<NewsContentEntity> GetContentListByIndexAndAmount(int contentCategoryId, int startIndex = 0, int amount = 0)
        {
            List<string> wheres = new List<string>();
            DynamicParameters parameters = new DynamicParameters();
            wheres.Add("A.Enabled=1");
            wheres.Add("A.ContentCategoryId=?ContentCategoryId");
            parameters.Add("?ContentCategoryId", contentCategoryId, DbType.Int32);

            string newsql = string.Format("{0} {1} Order By A.CreateTime DESC LIMIT {2},{3}; SELECT COUNT(1) FROM newscontent A {1}",
                sql,
                wheres.Count > 0 ? "WHERE " + string.Join(" AND ", wheres) : string.Empty,
                startIndex, amount);

            var results = this.databaseContext.Slave.Query<NewsContentEntity>(newsql, parameters);
            return results;
        }

        public IEnumerable<NewsContentViewEntity> GetViewPageableList(int? contentCategoryId, string title, string keyWord, bool fromSim, out int dataCount, bool? ascending, int pageIndex, int pageSize)
        {
            List<string> wheres = new List<string>();
            DynamicParameters parameters = new DynamicParameters();
            wheres.Add("A.Enabled=1");
            if (!string.IsNullOrWhiteSpace(keyWord))
            {
                wheres.Add("A.KeyWord=?KeyWord");
                parameters.Add("?KeyWord", keyWord, DbType.AnsiString);
            }
            if (!string.IsNullOrWhiteSpace(title))
            {
                wheres.Add("A.Title LIKE CONCAT('%',?Title,'%')");
                parameters.Add("?Title", title, DbType.AnsiString);
            }
            if (contentCategoryId.HasValue)
            {
                wheres.Add("A.ContentCategoryId=?ContentCategoryId");
                parameters.Add("?ContentCategoryId", contentCategoryId, DbType.Int32);
            }
            if (!fromSim)
            {
                wheres.Add("A.IsDelete=0");
            }
            string newsql = string.Format(@"{0} {1} ORDER BY A.CreateTime DESC LIMIT {2}, {3}; 
                    SELECT COUNT(1) FROM newscontent A INNER JOIN contentcategory B ON A.ContentCategoryId = B.ContentCategoryId {1}",
                viewsql,
                wheres.Count > 0 ? "WHERE " + string.Join(" AND ", wheres) : string.Empty,
                (pageIndex == 0 ? 0 : pageIndex - 1) * pageSize,
                pageSize,
                ascending.HasValue && ascending.Value ? "ASC" : "DESC");

            var results = this.databaseContext.Slave.QueryMultiple<NewsContentViewEntity, int>(newsql, parameters);
            dataCount = results.Item2;
            return results.Item1;
        }

        public NewsContentEntity GetNewsContentById(long newsContentId, bool fromSim)
        {
            DynamicParameters parameters = new DynamicParameters();
            parameters.Add("?NewsContentId", newsContentId, DbType.Int64);
            if (!fromSim)
            {
                string tempSql = sqlbyID;
                tempSql += " AND A.IsDelete=0 AND A.Enabled=1";
                return this.databaseContext.Slave.QueryEntity<NewsContentEntity>(tempSql, parameters);
            }
            else
            {
                return this.databaseContext.Slave.QueryEntity<NewsContentEntity>(sqlbyID, parameters);
            }
        }

        public NewsContentEntity UpdateEntity(NewsContentEntity entity)
        {
            string updateSql = @"UPDATE newscontent SET 
                                    Title=?Title,
                                    SubTitle=?SubTitle,
                                    Content=?Content,
                                    AdminId=?AdminId,
                                    ContentCategoryId=?ContentCategoryId,
                                    ViewsCount=?ViewsCount,
                                    IsComment=?IsComment,
                                    KeyWord=?KeyWord,
                                    Summary=?Summary,
                                    ImageUrl=?ImageUrl,
                                    Link=?Link 
                                WHERE NewsContentId=?NewsContentId;";
            string newsql = string.Format(@"{0} {1}", updateSql, sqlbyID);
            DynamicParameters parameters = new DynamicParameters();
            parameters.Add("?NewsContentId", entity.NewsContentId, DbType.Int64);
            parameters.Add("?Title", entity.Title, DbType.AnsiString);
            parameters.Add("?SubTitle", entity.SubTitle, DbType.AnsiString);
            parameters.Add("?Content", entity.Content, DbType.AnsiString);
            parameters.Add("?AdminId", entity.AdminId, DbType.Int64);
            parameters.Add("?ContentCategoryId", entity.ContentCategoryId, DbType.Int32);
            parameters.Add("?ViewsCount", entity.ViewsCount, DbType.Int32);
            parameters.Add("?KeyWord", entity.KeyWord, DbType.AnsiString);
            parameters.Add("?Summary", entity.Summary, DbType.AnsiString);
            parameters.Add("?ImageUrl", entity.ImageUrl, DbType.AnsiString);
            parameters.Add("?Link", entity.Link, DbType.AnsiString);
            parameters.Add("?IsComment", entity.IsComment, DbType.Boolean);
            return this.databaseContext.Master.QueryEntity<NewsContentEntity>(newsql, parameters,databaseContext.Transaction);
        }

        public IEnumerable<NewsContentEntity> DeleteEntity(long newsContentId)
        {
            string newsql = string.Format(@"UPDATE newscontent SET IsDelete=1 WHERE NewsContentId =?NewsContentId;{0}", sql);
            DynamicParameters parameters = new DynamicParameters();
            parameters.Add("?NewsContentId", newsContentId, DbType.Int64);
            return this.databaseContext.Master.Query<NewsContentEntity>(newsql, parameters,databaseContext.Transaction);
        }

        public bool UpdateViewsCount(long newsContentId)
        {
            string newsql = "UPDATE newscontent SET ViewsCount=ViewsCount+1 WHERE NewsContentId=?NewsContentId;";
            DynamicParameters parameters = new DynamicParameters();
            parameters.Add("?NewsContentId", newsContentId, DbType.Int64);
            return this.databaseContext.Master.Execute(newsql, parameters,databaseContext.Transaction) > 0;
        }

        public void UpdateLikeCount(long newsContentId)
        {
            string updateSql = @"UPDATE NewsContent SET
                            LikeCount=LikeCount+1
                           WHERE NewsContentId=?NewsContentId AND LikeCount>=0";
            DynamicParameters parameters = new DynamicParameters();
            parameters.Add("?NewsContentId", newsContentId, DbType.Int64);
            this.databaseContext.Master.Execute(updateSql, parameters, databaseContext.Transaction);
        }

        public void UpdateUnLikeCount(long newsContentId)
        {
            string updateSql = @"UPDATE NewsContent SET
                            LikeCount=LikeCount-1
                           WHERE NewsContentId=?NewsContentId AND LikeCount>=0";
            DynamicParameters parameters = new DynamicParameters();
            parameters.Add("?NewsContentId", newsContentId, DbType.Int64);
            this.databaseContext.Master.Execute(updateSql, parameters, databaseContext.Transaction);
        }

        public void UpdateReplyCount(long newsContentId)
        {
            string updateSql = @"UPDATE NewsContent SET
                            ReplyCount=ReplyCount+1
                           WHERE NewsContentId=?NewsContentId";
            DynamicParameters parameters = new DynamicParameters();
            parameters.Add("?NewsContentId", newsContentId, DbType.Int64);
            this.databaseContext.Master.Execute(updateSql, parameters, databaseContext.Transaction);
        }

        public NewsContentEntity UpdateReprintCount(long newsContentId)
        {
            string updateSql = @"UPDATE NewsContent SET
                            ReprintCount=ReprintCount+1
                           WHERE NewsContentId=?NewsContentId";
            DynamicParameters parameters = new DynamicParameters();
            parameters.Add("?NewsContentId", newsContentId, DbType.Int64);
            return this.databaseContext.Master.QueryEntity<NewsContentEntity>(string.Format("{0};{1} WHERE NewsContentId=?NewsContentId", updateSql, sql), parameters, databaseContext.Transaction);
        }

        public IEnumerable<long> GetNewsContentIds()
        {
            string tempSql = @"SELECT NewsContentId FROM NewsContent WHERE Enabled=1";
            return this.databaseContext.Slave.Query<long>(tempSql);
        }

        public int GetNewsCount()
        {
            string tempSql = @"SELECT COUNT(1) FROM NewsContent WHERE Enabled=1";
            return this.databaseContext.Slave.ExecuteScalar<int>(tempSql);
        }

        public NewsContentEntity GetNewsContentByProtocol(string protocolType)
        {
            string protocolSql = string.Format("{0} WHERE Enabled=1 AND ProtocolType=?ProtocolType", sql);
            DynamicParameters parameters = new DynamicParameters();
            parameters.Add("?ProtocolType", protocolType?.Trim(), DbType.AnsiString);
            return this.databaseContext.Slave.QueryEntity<NewsContentEntity>(protocolSql, parameters);
        }

        public IEnumerable<NewsContentEntity> QueryNewsByKeyWord(int? contentCategoryId, string keyWord, out int dataCount, int pageIndex = 1, int pageSize = 20)
        {
            List<string> wheres = new List<string>();
            DynamicParameters parameters = new DynamicParameters();
            wheres.Add("A.Enabled=1");
            if (contentCategoryId.HasValue)
            {
                wheres.Add("A.ContentCategoryId=?ContentCategoryId");
                parameters.Add("?ContentCategoryId", contentCategoryId, DbType.Int32);
            }
            if (!string.IsNullOrWhiteSpace(keyWord))
            {
                wheres.Add("KeyWord LIKE CONCAT('%',CONCAT(?KeyWord,'%'))");
                parameters.Add("?KeyWord", keyWord, DbType.AnsiString, size: 20);
            }

            wheres.Add("IsDelete=0");

            string newsql = string.Format("{0} {1} Order By A.CreateTime DESC LIMIT {2},{3}; SELECT COUNT(1) FROM newscontent A {1}",
                sql,
                wheres.Count > 0 ? "WHERE " + string.Join(" AND ", wheres) : string.Empty,
                (pageIndex == 0 ? 0 : pageIndex - 1) * pageSize,
                pageSize);

            var results = this.databaseContext.Slave.QueryMultiple<NewsContentEntity, int>(newsql, parameters);
            dataCount = results.Item2;
            return results.Item1;
        }

        #region 启用新闻内容
        public async Task<bool> Enabled(long newsContentId)
        {
            string sql = @"UPDATE newscontent SET Enabled=1 WHERE NewsContentId=?NewsContentId";
            DynamicParameters parameters = new DynamicParameters();
            parameters.Add("?NewsContentId", newsContentId, DbType.Int64);
            return await this.databaseContext.Master.ExecuteAsync(sql,parameters, databaseContext.Transaction) > 0;
        }
        #endregion

        #region 禁用新闻内容
        public async Task<bool> Disabled(long newsContentId)
        {
            string sql = @"UPDATE newscontent SET Enabled=0 WHERE NewsContentId=?NewsContentId";
            DynamicParameters parameters = new DynamicParameters();
            parameters.Add("?NewsContentId", newsContentId, DbType.Int64);
            return await this.databaseContext.Master.ExecuteAsync(sql, parameters, databaseContext.Transaction) > 0;
        }

        public NewsContentEntity GetNewsContentNextOrPreviou(long newsContentId, bool isNext)
        {
            string sql = string.Format(@"SELECT A.NewsContentId,A.Title,A.SubTitle,A.Content,
                                  A.IsDelete,A.CreateTime,A.AdminId,A.ContentCategoryId,A.Enabled,
                                  A.ViewsCount,A.IsComment,A.KeyWord,A.Summary,A.ImageUrl,A.Link,B.CategoryName,A.ReprintCount,A.LikeCount,A.ReplyCount,
                                  C.LoginName
                            FROM newscontent A 
                                LEFT JOIN contentcategory B ON B.ContentCategoryId = A.ContentCategoryId 
                                INNER JOIN admin C ON A.AdminId=C.AdminId
                            WHERE A.ContentCategoryId =(SELECT ContentCategoryId FROM newscontent WHERE NewsContentId=?NewsContentId) AND A.NewsContentId {0} ?NewsContentId LIMIT 1;",
                            isNext ? ">" : "<");
            DynamicParameters parameters = new DynamicParameters();
            parameters.Add("?NewsContentId", newsContentId, DbType.Int64);
            return this.databaseContext.Slave.QueryEntity<NewsContentEntity>(sql, parameters);
        }
        #endregion

    }
}
